package com.infore.qualityControl.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.qualityControl.entity.MonthQualityIntegrated;

@Repository
public interface IMonthQualityIntegratedRepository
		extends JpaRepository<MonthQualityIntegrated, String>, JpaSpecificationExecutor<String> {
	/** 根据站点ID查询 **/
	List<MonthQualityIntegrated> findByStationCodeIn(List<String> stationCodes);

	/** 根据站点ID分页查询 **/
	Page<MonthQualityIntegrated> findByStationCodeIn(List<String> stationCodes, Pageable pageable);
	
	/** 根据站点ID、监测项目、时间倒序查询 **/
	List<MonthQualityIntegrated> findBySubmitTimeBetweenAndIndexCodeAndStationCodeInOrderBySubmitTimeDesc(Date startDate,
			Date endDate, String indexCode, List<String> stationCodes);

	/** 根据站点ID、监测项目、时间倒序分页查询 **/
	Page<MonthQualityIntegrated> findBySubmitTimeBetweenAndIndexCodeAndStationCodeInOrderBySubmitTimeDesc(Date startDate,
			Date endDate, String indexCode, List<String> stationCodes, Pageable pageable);

	/** 根据监测项目分组获取最新数据 **/
	@Query(value = "SELECT A.* FROM MONTH_QUALITY_INTEGRATED A,"
			+ "(SELECT INDEX_CODE,MAX(SUBMIT_TIME) SUBMIT_TIME FROM MONTH_QUALITY_INTEGRATED WHERE STATION_CODE IN (:stationCodes) GROUP BY INDEX_CODE) B "
			+ "WHERE A.SUBMIT_TIME = B.SUBMIT_TIME AND A.INDEX_CODE = B.INDEX_CODE", nativeQuery = true)
	List<MonthQualityIntegrated> getLatestData(@Param("stationCodes") List<String> stationCodes);
	
	/** 根据站点ID获取最新的参数配置时间 **/
	@Query(value = "SELECT INDEX_CODE,TO_CHAR(B.SUBMIT_TIME,'yyyy-MM-dd HH24:mi:ss') SUBMIT_TIME FROM "
			+ "(SELECT MAX(SUBMIT_TIME) SUBMIT_TIME "
			+ "FROM MONTH_QUALITY_INTEGRATED WHERE STATION_CODE = :stationCode) A "
			+ "JOIN MONTH_QUALITY_INTEGRATED B ON A.SUBMIT_TIME = B.SUBMIT_TIME ", nativeQuery = true)
	List<Map<String, Object>> getSingleLatestData(@Param("stationCode") String stationCode);
}
